package com.softer.chengxin.score.dao;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.score.dao.provider.ManagerDaoProvider;
import com.softer.chengxin.score.entity.ScoreDetail;
import com.softer.chengxin.score.entity.ScoreDetailVO;
import com.softer.chengxin.score.entity.ScoreRecord;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @author wangixnrui
 * @date 2018/10/12
 */
@Mapper
@Repository
public interface SDManagerDao {

    /**
     * 查询企业信用年度评价撇比
     *
     * @param area   管理员所管理地区
     * @param qyName 企业名
     * @param dj     等级
     * @param year   年度
     * @param page   页码
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "findSRbyCondition")
    List<ScoreRecord> findByCondition(@Param("area") String area,
                                      @Param("qyName") String qyName,
                                      @Param("dj") String dj,
                                      @Param("year") String year,
                                      @Param("page") Page page);

    /**
     * 查询企业信用年度评价列表 条数
     *
     * @param area   管理员所管理地区
     * @param qyName 企业名
     * @param dj     等级
     * @param year   年度
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "findSRbyConditionCount")
    int findByConditionCount(@Param("area") String area,
                             @Param("qyName") String qyName,
                             @Param("dj") String dj,
                             @Param("year") String year);

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表
     *
     * @param infoType 信息种类
     * @param area     区域
     * @param page     页码
     * @param qyName   企业名称
     * @param proName  项目名称
     * @param state    状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailList")
    List<ScoreDetailVO> getScoreDetailList(@Param("infoType") Integer infoType,
                                           @Param("area") String area,
                                           @Param("page") Page page,
                                           @Param("qyName") String qyName,
                                           @Param("proName") String proName,
                                           @Param("state") String state);

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表 条数
     *
     * @param infoType 信息种类
     * @param area     区域
     * @param qyName   企业名称
     * @param proName  项目名称
     * @param state    状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailListCount")
    int getScoreDetailListCount(@Param("infoType") Integer infoType,
                                @Param("area") String area,
                                @Param("qyName") String qyName,
                                @Param("proName") String proName,
                                @Param("state") String state);

    /**
     * 查询账号解绑
     *
     * @param name 用户名
     * @return 结果
     */
    @Select("select role_id from manager_user where login_name=#{name}")
    int getUserRoleId(@Param("name") String name);

    /**
     * 查询生效开始时间
     *
     * @param id
     * @return 开始时间
     */
    @Select("select effective_begin_time AS beginTime from score_detail where id = #{id}")
    String getEffectiveBeginTimeById(Integer id);


    /**
     * 查询审核状态
     * @param id
     * @return 开始时间
     */
    @Select("SELECT state from score_detail WHERE id = #{id}")
    String getStateById(Integer id);

    /**
     * 删除优良/负面信息
     *
     * @param id id
     * @return 结果
     */
    @Update("DELETE FROM score_detail WHERE id=#{id}")
    int deleteScore(@Param("id") Integer id);
    /**
     * 根据信息ID查询企业ID
     * @param id 信息ID
     * @return 企业ID
     */
    @Select("select corp_id from score_detail where id=#{id}")
    Integer getCorpId(@Param("id") Integer id);
    /**
     * 获取负面/优良信息录入界面的信息列表
     *
     * @param adder   添加人（录入中显示该账号添加的信息）
     * @param qyName  企业名
     * @param proName 项目名
     * @param page    页码
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreAddList")
    List<ScoreDetailVO> getScoreAddList(@Param("adder") String adder,
                                        @Param("qyName") String qyName,
                                        @Param("proName") String proName,
                                        @Param("infoType") Integer infoType,
                                        @Param("page") Page page);

    @Select("select project_name from company_project where id=#{id}")
    String findProNameById(@Param("id") String projectId);

    /**
     * 获取负面/优良信息录入界面的信息列表 条数
     *
     * @param adder    添加人（录入中显示该账号添加的信息）
     * @param qyName   企业名
     * @param proName  项目名
     * @param infoType 信息种类
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreAddListCount")
    int getScoreAddListCount(@Param("adder") String adder,
                             @Param("qyName") String qyName,
                             @Param("proName") String proName,
                             @Param("infoType") Integer infoType);

    /**
     * 新增  保存  负面/优良信息
     *
     * @param scoreDetailVO 信息实体
     * @return 结果
     */
    @Insert("insert into score_detail(score_type_id,score,corp_id,project_id,adder," +
            "remark,reason,state,approver,approver_time,effective_begin_time," +
            "effective_end_time,info_type,bjdw,bjsj,hjmc,file_path_id,shyj,ssnr," +
            "ssfj_id,sssj,hjxm,gmt_create,gmt_modified,publicity_begin_time," +
            "publicity_end_time) values(#{s.scoreTypeId},#{s.score},#{s.corpId}," +
            "#{s.projectId},#{s.adder},#{s.remark},#{s.reason},#{s.state},#{s.adder}," +
            "#{s.approverTime},#{s.effectiveBeginTime},#{s.effectiveEndTime}," +
            "#{s.infoType},#{s.bjdw},#{s.bjsj},#{s.hjmc},#{s.filePathId},#{s.shyj}," +
            "#{s.ssnr},#{s.ssfjId},#{s.sssj},#{s.hjxm},now(),#{s.gmtModified}," +
            "#{s.publicityBeginTime},#{s.publicityEndTime})")
    @Options(useGeneratedKeys = true, keyProperty = "s.id", keyColumn = "id")
    void addScore(@Param("s") ScoreDetailVO scoreDetailVO);

    /**
     * 公示
     *
     * @param adder 登录名
     * @param id    id
     * @return 结果
     */
    @Update("update score_detail set state=2 , gmt_modified=#{publicBegin},approver_time=now() " +
            ",publicity_begin_time=#{publicBegin},publicity_end_time=#{publicEnd} " +
            ",effective_begin_time=#{publicEnd} where id=#{id}")
    int publicity(@Param("adder") String adder,
                  @Param("id") Integer id,
                  @Param("publicBegin") Date publicBegin,
                  @Param("publicEnd") Date publicEnd);

    /**
     * 修改信息
     *
     * @param scoreDetailVO 参数
     * @return 结果
     */
    @Update("update score_detail set score_type_id=#{s.scoreTypeId},score=#{s.score}," +
            "corp_id=#{s.corpId},project_id=#{s.projectId},adder=#{s.adder}," +
            "remark=#{s.remark},reason=#{s.reason},state=0," +
            "info_type=#{s.infoType},bjdw=#{s.bjdw},bjsj=#{s.bjsj}," +
            "file_path_id=#{s.filePathId},effective_end_time=#{s.effectiveEndDate}," +
            "gmt_modified=now() where id=#{s.id}")
    int update(@Param("s") ScoreDetailVO scoreDetailVO);

    /**
     * 删除保存的信息
     *
     * @param id 信息id
     * @return 结果
     */
    @Delete("delete from score_detail where id=#{id}")
    int delete(@Param("id") Integer id);

    /**
     * 通过id查询信息
     *
     * @param id id
     * @return 结果
     */
    @Select("select d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score," +
            "d.corp_id as corpId,d.project_id as projectId,d.adder as adder," +
            "d.remark as remark,d.reason as reason,d.state as state,d.approver as approver," +
            "DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,d.effective_begin_time as effectiveBeginTime," +
            "DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType," +
            "d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId," +
            "d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj," +
            "d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified," +
            "DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as " +
            "publicityEndTime  from score_detail d where d.id=#{id}")
    ScoreDetailVO findScoreDetailById(@Param("id") Integer id);

    /**
     * 审批优良/负面信息
     *
     * @param id                 信息id
     * @param score              得分
     * @param scoreTypeId        得分类型
     * @param effectiveBeginTime 生效开始时间
     * @param effectiveEndTime   生效结束时间
     * @param remark             备注
     * @param state              状态
     * @return 结果
     */
    @Update("update score_detail set score=#{score},state=#{state},gmt_modified=now()," +
            "effective_begin_time=#{effectiveBeginTime},score_type_id=#{scoreTypeId}," +
            "effective_end_time=#{effectiveEndTime},shyj=#{remark},approver=#{loginName}," +
            "approver_time=now(),state=#{state},publicity_begin_time=now()," +
            "publicity_end_time=#{effectiveBeginTime}  where id=#{id}")
    int sp(@Param("id") Integer id,
           @Param("score") String score,
           @Param("scoreTypeId") Integer scoreTypeId,
           @Param("effectiveBeginTime") String effectiveBeginTime,
           @Param("effectiveEndTime") String effectiveEndTime,
           @Param("remark") String remark,
           @Param("loginName") String loginName,
           @Param("state") Integer state);

    /**
     * 查询当前记录的状态
     *
     * @param id 记录id
     * @return 状态
     */
    @Select(" SELECT state FROM `score_detail` WHERE id=#{id}")
    Integer getState(@Param("id") Integer id);

    /**
     * 审批不通过时调用此sql
     *
     * @param state 状态
     * @return 结果
     */

    @UpdateProvider(type = ManagerDaoProvider.class, method = "spNo")
    int spNo(@Param("id") Integer id,
             @Param("state") Integer state,
             @Param("shyj") String shyj,
             @Param("approver") String approver,@Param("ssnr") String ssnr,
             @Param("score") String score,@Param("scoreTypeId") String scoreTypeId);

    /**
     * 变更
     *
     * @param scoreDetailVO 实体
     * @return 结果
     */
    @UpdateProvider(type = ManagerDaoProvider.class, method = "biangeng")
    int biangeng(ScoreDetailVO scoreDetailVO);

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）
     *
     * @param qyName             企业名
     * @param infoType           信息种类
     * @param effectiveBeginTime 公示开始时间
     * @param effectiveEndTime   公示结束时间
     * @param corpId             企业id
     * @param year               年度
     * @param area               管理区域
     * @param page               页码
     * @return 结果集
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailByYear")
    List<ScoreDetailVO> getScoreDetailByYear(@Param("area") String area,
                                             @Param("qyName") String qyName,
                                             @Param("infoType") Integer infoType,
                                             @Param("effectiveBeginTime") String effectiveBeginTime,
                                             @Param("effectiveEndTime") String effectiveEndTime,
                                             @Param("corpId") Integer corpId,
                                             @Param("year") String year,
                                             @Param("page") Page page);

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）条数
     *
     * @param qyName             企业名
     * @param infoType           信息种类
     * @param effectiveBeginTime 公示开始时间
     * @param effectiveEndTime   公示结束时间
     * @param corpId             企业id
     * @param year               年度
     * @param area               管理区域
     * @return 结果集
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailByYearCount")
    int getScoreDetailByYearCount(@Param("area") String area,
                                  @Param("qyName") String qyName,
                                  @Param("infoType") Integer infoType,
                                  @Param("effectiveBeginTime") String effectiveBeginTime,
                                  @Param("effectiveEndTime") String effectiveEndTime,
                                  @Param("corpId") Integer corpId,
                                  @Param("year") String year);

    /**
     * 得到企业该年度的得分信息
     *
     * @param poguid 企业id
     * @param year   年度
     * @return 结果
     */
    @Select("select r.id as id,r.jc_01 as jc01,r.jc_02 as jc02,r.jc_03 as jc03,r.jc_04 as jc04,r.jc_05 as jc05,r.jc_06 as jc06,r.jc_07 as jc07,r.yl_01 as yl01,r.yl_02 as yl02,r.yl_03 as yl03,r.yl_04 as yl04,r.yl_05 as yl05,r.yl_06 as yl06,r.yl_07 as yl07,r.yl_08 as yl08,r.fm_01 as fm01,r.fm_02 as fm02,r.fm_03 as fm03,r.fm_04 as fm04,r.fm_05 as fm05,r.fm_06 as fm06,r.fm_07 as fm07,r.all_score as allScore,r.poguid as poguid,DATE_FORMAT(r.gmt_create, '%Y-%m-%d') as gmtCreate,r.dj as dj,r.year as year,DATE_FORMAT(r.gmt_modified, '%Y-%m-%d') as gmtModified " +
            " from score_record r where r.poguid=#{poguid} and r.year=#{year}")
    ScoreRecord getScoreRecord(@Param("poguid") Integer poguid, @Param("year") String year);

    /**
     * 获取该企业该年度得分详细
     *
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select id as id,score_type_id as scoreTypeId,(SELECT score_name FROM score_type WHERE id = score_type_id) AS scoreTypeName,score as score,info_type as infoType from score_detail " +
            "where corp_id = #{corpId} AND publicity_begin_time <= NOW()" +
            " AND effective_end_time >= NOW() and state in (2,4,5,6,7)")
    List<ScoreDetail> getScoreDetails(@Param("corpId") Integer corpId);

    /**
     * 修改该企业当前年度的record表信息
     *
     * @param scoreRecord record
     * @return 结果
     */
    @Update("update score_record set jc_01=#{r.jc01},jc_02=#{r.jc02},jc_03=#{r.jc03}," +
            "jc_04=#{r.jc04},jc_05=#{r.jc05},jc_06=#{r.jc06},jc_07=#{r.jc07}," +
            "yl_01=#{r.yl01},yl_02=#{r.yl02},yl_03=#{r.yl03},yl_04=#{r.yl04}," +
            "yl_05=#{r.yl05},yl_06=#{r.yl06},yl_07=#{r.yl07},yl_08=#{r.yl08}," +
            "fm_01=#{r.fm01},fm_02=#{r.fm02},fm_03=#{r.fm03},fm_04=#{r.fm04}," +
            "fm_05=#{r.fm05},fm_06=#{r.fm06},fm_07=#{r.fm07},all_score=#{r.allScore}," +
            "dj=#{r.dj},gmt_modified=now() where id=#{r.id}")
    int updateScoreRecord(@Param("r") ScoreRecord scoreRecord);

    @Insert("insert into score_record(jc_01,jc_02,jc_03,jc_04,jc_05,jc_06,jc_07," +
            "yl_01,yl_02,yl_03,yl_04,yl_05,yl_06,yl_07,yl_08," +
            "fm_01,fm_02,fm_03,fm_04,fm_05,fm_06,fm_07," +
            "all_score,poguid,dj,year,gmt_create) " +
            "values(#{r.jc01},#{r.jc02},#{r.jc03},#{r.jc04},#{r.jc05},#{r.jc06},#{r.jc07}," +
            "#{r.yl01},#{r.yl02},#{r.yl03},#{r.yl04},#{r.yl05},#{r.yl06},#{r.yl07},#{r.yl08}," +
            "#{r.fm01},#{r.fm02},#{r.fm03},#{r.fm04},#{r.fm05},#{r.fm06},#{r.fm07}," +
            "#{r.allScore},#{r.poguid},#{r.dj},#{r.year},now())")
    int insertScoreRecord(@Param("r") ScoreRecord scoreRecord);

    /**
     * 查询得分信息详情
     *
     * @param id 得分信息id
     * @return 结果
     */
    @Select("select zg_bgyj AS zgBgyj,c.`name` as qyName,p.project_name as proName,d.id as id,d.score_type_id as scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime " +
            " from score_detail d LEFT JOIN company_base_info c on c.id=d.corp_id LEFT JOIN company_project p ON p.id=d.project_id where d.id=#{id}")
    ScoreDetailVO getDetailScore(@Param("id") Integer id);

    /**
     * 获取变更列表
     * @param infoType 信息种类
     * @param page     页码
     * @param qyName   企业名
     * @param proName  项目名
     * @param loginName  登录名
     * @param state  状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getBianGengList")
    List<ScoreDetailVO> getBianGengList(@Param("infoType") Integer infoType,
                                        @Param("page") Page page,
                                        @Param("qyName") String qyName,
                                        @Param("proName") String proName,
                                        @Param("loginName") String loginName,
                                        @Param("state") String state);

    /**
     * 获取变更列表 条数
     *
     * @param infoType 信息种类
     * @param qyName   企业名
     * @param proName  项目名
     * @param loginName  登录名
     * @param state  状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getBianGengListCount")
    int getBianGengListCount(@Param("infoType") Integer infoType,
                             @Param("qyName") String qyName,
                             @Param("proName") String proName,
                             @Param("loginName") String loginName,
                             @Param("state") String state);

    /**
     * 通过用户名查找dic_code
     * @param loginName 用户名
     * @return 结果集
     */
    @Select("select dic_code from roules " +
            "where id=(select role_id from manager_user where login_name=#{loginName})")
    String getDicCodeByLoginName(@Param("loginName") String loginName);

    /**
     * 通过id查找dic_code
     * @param id 用户名
     * @return 结果集
     */
    @Select("select dic_code from roules " +
            "where id=(select role_id from manager_user where id=#{id})")
    String getDicCodeById(@Param("id") Integer id);

}
